package com.micro.magupe.jdbc.db;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

import com.micro.magupe.jdbc.dao.LogDao;
import com.micro.magupe.jdbc.entity.Log;

import io.shardingsphere.api.config.rule.ShardingRuleConfiguration;
import io.shardingsphere.api.config.rule.TableRuleConfiguration;
import io.shardingsphere.api.config.strategy.InlineShardingStrategyConfiguration;
import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

public class DBUtil {

    public static void sqlCrud() {
    	DataSource dataSource = new MicroDataSource(3);
    	Connection conn = null;
        Statement stmt = null;
        try {
            conn = dataSource.getConnection();
            
            conn.setAutoCommit(false);
            
            stmt = conn.createStatement();
            String sql = "select * from sys_log0";
            ResultSet rs = stmt.executeQuery(sql);
            
            conn.commit();
            
            while(rs.next()){
            	System.err.println(rs.getLong(1));
    		}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
    
    public static void sqlCrudByMybatis() {
		InputStream inStream = null;  
		inStream = DBUtil.class.getClassLoader().getResourceAsStream("mybatis-config.xml"); 
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inStream);
		
		SqlSession session = sqlSessionFactory.openSession();
		LogDao logDao = (LogDao) session.getMapper(LogDao.class);
		List<Log> list = logDao.list(new Log());
		list.forEach((log) -> {
			System.err.println(log.toString());
		});
    }
    
    private static ApplicationContext ctx;
    
    public static void sqlCrudBySpringJdbc() {
    	ctx = new ClassPathXmlApplicationContext("beans.xml");
    	JdbcTemplate jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
    	String sql = "select * from sys_log";
    	
    	List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        
    	list.forEach((map) -> {
        	System.err.println(map.toString());
        });
    }
    
    public static void sqlCrudByShardingJdbc() throws SQLException {
    	// 配置真实数据源
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        
        // 配置第一个数据源
        BasicDataSource dataSource1 = new BasicDataSource();
        dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource1.setUrl("jdbc:mysql://127.0.0.1:3306/micro_magupe_log_master_dev?useUnicode=true&characterEncoding=utf8&useSSL=false");
        dataSource1.setUsername("root");
        dataSource1.setPassword("880204");
        dataSourceMap.put("ds0", dataSource1);
        
        // 配置第二个数据源
        BasicDataSource dataSource2 = new BasicDataSource();
        dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource2.setUrl("jdbc:mysql://127.0.0.1:3306/micro_magupe_log_slave_dev?useUnicode=true&characterEncoding=utf8&useSSL=false");
        dataSource2.setUsername("root");
        dataSource2.setPassword("880204");
        dataSourceMap.put("ds1", dataSource2);
        
        // 配置sys_log表规则
        TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
        orderTableRuleConfig.setLogicTable("sys_log");
        orderTableRuleConfig.setActualDataNodes("ds${0..1}.sys_log${0..9}");
        
        // 配置分库 + 分表策略
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "ds${id % 2}"));
        orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("id", "sys_log${id % 10}"));
        
        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
        
        // 获取数据源对象
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap<String, Object>(), new Properties());
        
        String sql = "select * from sys_log";
        try (Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) {
            try (ResultSet rs = stmt.executeQuery(sql)) {
                while(rs.next()) {
                	System.err.println(rs.getLong(1));
                }
            }
        }
        
    }
    
    public static void sqlCrudByPool() {
    	Connection conn = null;
        Statement stmt = null;
        try {
            conn = PoolUtil.getConnection();
            
            conn.setAutoCommit(false);
            
            stmt = conn.createStatement();
            String sql = "select * from sys_log";
            ResultSet rs = stmt.executeQuery(sql);
            
            conn.commit();
            
            while(rs.next()){
            	System.err.println(rs.getLong(1));
    		}
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
    }
}
